if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_fb_leave')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_fb_leave
go


create procedure bd_proc_schedule_fb_leave (@user_serial bigint,@date varchar(10),@operator varchar(50)) as
BEGIN
	--过程备注：排班时定义当日是否是放班状态，而进一步确定是否需要生成、删除对应的放班请假单和明细
	--思路：
			--1、根据入参，获取该人员当日是否有排班，进一步确定排班的符号（''、'[A]'、'[X]'、'[F]'）
			--2、根据符号分两类逻辑：
				--（1）、符号为[F]:即当日是放班状态，则要检索请假单和明细中是否存在该天为放班的请假信息
					--无则新增（自动审批）
				--（2）、符号为''、'[A]'、'[X]'：即非[F],当日非放班状态，
					--则要检索请假单和明细中是否存在该天为放班的请假信息，
					--存在则删除（逻辑删除，逻辑删除之后不能重新激活，
					--若需重新放班，需重走放班操作，1排班指定放班，2请放班假）


	DECLARE @flag  varchar(5) = ''; --排班符号
	DECLARE @columnName  varchar(10); --kt_paiba表字段名（d1、d2、d3......），根据@date 获取要查询的字段名。
	DECLARE @rq  varchar(7);
	DECLARE @counts int; --记录条数

	DECLARE @leave_bh varchar(10)='10003'; --请假类型BH
	DECLARE @leave_minute int; --请假分钟数
	DECLARE @begin_time varchar(10); -- 开始时间 HH:MM
	DECLARE @end_time varchar(10); --结束时间 HH:MM
	DECLARE @leave_minute_old int; --旧的请假分钟数
	DECLARE @order_id int; --请假单ID
	DECLARE @order_no varchar(20); --请假单ID


	--赋值@columnName
	set @columnName = concat('d',convert(int,SUBSTRING(@date, 9, 2)))
	set @rq = SUBSTRING(@date, 1, 7)

	--1、获取排班符号
	DECLARE @sql nvarchar(max) = N'';
	DECLARE @ParmDefinition nvarchar(max) = N'';
	set @sql = N'select @flag = SUBSTRING('+@columnName+', 1, 3) from kt_paiba where user_serial = @user_serial and rq = @rq';
	set @ParmDefinition=N'@user_serial bigint,@rq varchar(7),@flag varchar(5) output';
	exec sp_executesql @sql,@ParmDefinition,@user_serial,@rq, @flag output;--注意@flag的值，调试时不能使用print,会转义[]，导致打印为空。

	--查找是否已存在放班请假明细（过滤无效的记录）
	select @counts = count(1)
		from lr_kqingj k
		INNER JOIN bd_leave_order o  on o.order_id = k.order_id
		where o.is_del = 0
					and convert(varchar(10),k.kssj,120) = @date
					and k.user_serial = @user_serial
					and k.whether_validity = 1
					and k.bh = @leave_bh;

	--2、排班状态为[F]
	if @flag = '[F]'
		BEGIN

			--获取请假时长(新增或修改用)
			select @leave_minute =  sum(s.allt)
				from zt_shid s
				INNER JOIN zt_banci b on b.bh = s.parent
				where b.id in (
					select  shift_id from bd_kq_schedule_detail where user_serial = @user_serial and shift_date = @date
				)
				and s.lx = 0;

			--获取开始时间和结束时间(新增或修改用)
			select @begin_time =  case when min(s.sbsj) < 0 then '-'+ dbo.bd_fn_numToMin(-min(s.sbsj)) else dbo.bd_fn_numToMin(min(s.sbsj)) end ,
						@end_time = case when max(s.xbsj) < 0 then '-'+ dbo.bd_fn_numToMin(-max(s.xbsj)) else dbo.bd_fn_numToMin(max(s.xbsj)) end
				from zt_shid s
				INNER JOIN zt_banci b on b.bh = s.parent
				where b.id in (
					select  shift_id from bd_kq_schedule_detail where user_serial = @user_serial and shift_date = @date
				)
			and s.lx = 0;

			--不存在，则新增请假单和明细，请假时长取排班中的信息（多个班次取合计）开始时间、结束时间取多个班次的最早上班时间和最晚下班时间
			if @counts = 0
				BEGIN
				print 'is'
					--生成单号和ID
					exec bd_proc_sys_getMaxNumber '5',@order_no output
					exec bd_proc_sys_getMaxId 5,@order_id output

					--插入请假单
					insert into bd_leave_order(
						order_id,
						order_no,
						leave_id,
						start_date,
						end_date,
						people_num,
						total_time,
						remark,
						creater_id,
						apply_type,
						status,
						begin_time,
						end_time
					)values(
						@order_id,
						@order_no,
						@leave_bh,
						@date,
						@date,
						1,
						@leave_minute,
						'排班自动生成',
						@operator,
						2,
						4,
						@begin_time,
						@end_time
					);
					--插入明细
					insert into lr_kqingj(
						user_serial,
						bh,
						kssj,
						jssj,
						jsjg,
						gly_no,
						order_id,
						settle_year,
						settle_month
					)VALUES(
						@user_serial,
						@leave_bh,
						@date,
						@date,
						@leave_minute,
						@operator,
						@order_id,
						DATEPART(year, dbo.bd_fn_getSettleDate(@date)),
						DATEPART(month, dbo.bd_fn_getSettleDate(@date))
					);

				END
			else
				--存在，则更新请假单和明细
				BEGIN
					--获取旧数据
					select top 1 @leave_minute_old = jsjg,@order_id = order_id
						from lr_kqingj
						where convert(varchar(10),kssj,120) = @date and user_serial = @user_serial and bh = @leave_bh and whether_validity = 1;

					print @counts
					print @leave_minute_old
					print @order_id
					--更新请假单
					update bd_leave_order
						set total_time = total_time - @leave_minute_old + @leave_minute,
								begin_time = @begin_time,
								end_time = @end_time
						where order_id = @order_id;

					--更新明细
					update lr_kqingj
						set jsjg = @leave_minute
						where convert(varchar(10),kssj,120) = @date and user_serial = @user_serial and bh = @leave_bh and order_id = @order_id;
				END
		END
	ELSE
		--3、排班状态不为[F]
		BEGIN

			--查找是否已存在放班请假明细（过滤无效的记录）
			--存在，则删除明细，并检索请假单中是否全部已删除，若是，则该请假单也一并删除。
			if @counts>0
				BEGIN
					--获取请假单ID
					select top 1 @order_id = order_id
						from lr_kqingj
						where convert(varchar(10),kssj,120) = @date and user_serial = @user_serial and bh = @leave_bh and whether_validity = 1;
					--删除明细
					update lr_kqingj
						set whether_validity = 2
						where convert(varchar(10),kssj,120) = @date and user_serial = @user_serial and bh = @leave_bh and order_id = @order_id;

					--检索请假单
					select @counts = count(1)
						from lr_kqingj
						where order_id = @order_id and whether_validity = 1;

					--明细全部删除，删除请假单
					if @counts=0
						BEGIN
							--更新请假单
							update bd_leave_order
								set is_del = -1
								where order_id = @order_id;
						END
				END
		END
END
go
